-- PostgreSQL 数据库构建脚本
-- 基于 Prisma Schema 生成

-- 创建枚举类型
CREATE TYPE "JobStatus" AS ENUM (
    'OPEN',
    'DISTRIBUTED',
    'IN_PROGRESS',
    'COMPLETED',
    'CANCELLED',
    'EXPIRED'
);

CREATE TYPE "AgentWorkStatus" AS ENUM (
    'IDLE',
    'ASSIGNED',
    'WORKING',
    'COMPLETED',
    'FAILED',
    'CANCELLED',
    'TIMEOUT'
);

-- 创建分类表
CREATE TABLE "categories" (
    "id" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    
    CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
);

-- 创建 Agent 表
CREATE TABLE "agents" (
    "id" TEXT NOT NULL,
    "agentName" TEXT NOT NULL,
    "agentAddress" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "authorBio" TEXT NOT NULL,
    "agentClassification" TEXT NOT NULL,
    "tags" TEXT[],
    "isPrivate" BOOLEAN NOT NULL DEFAULT true,
    "autoAcceptJobs" BOOLEAN NOT NULL DEFAULT true,
    "contractType" TEXT NOT NULL DEFAULT 'result',
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "reputation" DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    "successRate" DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    "totalJobsCompleted" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "walletAddress" TEXT NOT NULL,
    
    CONSTRAINT "agents_pkey" PRIMARY KEY ("id")
);

-- 创建 Job 表
CREATE TABLE "jobs" (
    "id" TEXT NOT NULL,
    "jobTitle" TEXT NOT NULL,
    "category" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "deliverables" TEXT NOT NULL,
    "budget" JSONB NOT NULL,
    "maxBudget" DOUBLE PRECISION,
    "deadline" TIMESTAMP(3) NOT NULL,
    "paymentType" TEXT NOT NULL,
    "priority" TEXT NOT NULL,
    "skillLevel" TEXT NOT NULL,
    "tags" TEXT[],
    "status" "JobStatus" NOT NULL DEFAULT 'OPEN',
    "autoAssign" BOOLEAN NOT NULL DEFAULT false,
    "allowBidding" BOOLEAN NOT NULL DEFAULT true,
    "allowParallelExecution" BOOLEAN NOT NULL DEFAULT false,
    "escrowEnabled" BOOLEAN NOT NULL DEFAULT true,
    "isPublic" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "walletAddress" TEXT NOT NULL,
    
    CONSTRAINT "jobs_pkey" PRIMARY KEY ("id")
);

-- 创建任务分发记录表
CREATE TABLE "job_distribution_records" (
    "id" TEXT NOT NULL,
    "jobId" TEXT NOT NULL,
    "jobName" TEXT NOT NULL,
    "matchCriteria" JSONB NOT NULL,
    "totalAgents" INTEGER NOT NULL,
    "assignedCount" INTEGER NOT NULL DEFAULT 0,
    "responseCount" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "assignedAgentId" TEXT,
    "assignedAgentName" TEXT,
    
    CONSTRAINT "job_distribution_records_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "job_distribution_records_jobId_key" UNIQUE ("jobId")
);

-- 创建任务分发-Agent中间表
CREATE TABLE "job_distribution_agents" (
    "id" TEXT NOT NULL,
    "jobDistributionId" TEXT NOT NULL,
    "agentId" TEXT NOT NULL,
    "assignedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "workStatus" "AgentWorkStatus" NOT NULL DEFAULT 'ASSIGNED',
    "executionResult" TEXT,
    "startedAt" TIMESTAMP(3),
    "completedAt" TIMESTAMP(3),
    "progress" INTEGER,
    "errorMessage" TEXT,
    "executionTimeMs" INTEGER,
    "retryCount" INTEGER NOT NULL DEFAULT 0,
    
    CONSTRAINT "job_distribution_agents_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "job_distribution_agents_jobDistributionId_agentId_key" UNIQUE ("jobDistributionId", "agentId")
);

-- 添加外键约束
ALTER TABLE "job_distribution_records" 
ADD CONSTRAINT "job_distribution_records_jobId_fkey" 
FOREIGN KEY ("jobId") REFERENCES "jobs"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "job_distribution_agents" 
ADD CONSTRAINT "job_distribution_agents_jobDistributionId_fkey" 
FOREIGN KEY ("jobDistributionId") REFERENCES "job_distribution_records"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "job_distribution_agents" 
ADD CONSTRAINT "job_distribution_agents_agentId_fkey" 
FOREIGN KEY ("agentId") REFERENCES "agents"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- 创建索引以提高查询性能
CREATE INDEX "idx_agents_walletAddress" ON "agents"("walletAddress");
CREATE INDEX "idx_agents_agentClassification" ON "agents"("agentClassification");
CREATE INDEX "idx_agents_isActive" ON "agents"("isActive");
CREATE INDEX "idx_agents_reputation" ON "agents"("reputation" DESC);
CREATE INDEX "idx_agents_successRate" ON "agents"("successRate" DESC);

CREATE INDEX "idx_jobs_status" ON "jobs"("status");
CREATE INDEX "idx_jobs_category" ON "jobs"("category");
CREATE INDEX "idx_jobs_deadline" ON "jobs"("deadline");
CREATE INDEX "idx_jobs_walletAddress" ON "jobs"("walletAddress");
CREATE INDEX "idx_jobs_createdAt" ON "jobs"("createdAt" DESC);
CREATE INDEX "idx_jobs_priority" ON "jobs"("priority");
CREATE INDEX "idx_jobs_skillLevel" ON "jobs"("skillLevel");

CREATE INDEX "idx_job_distribution_records_jobId" ON "job_distribution_records"("jobId");
CREATE INDEX "idx_job_distribution_records_assignedAgentId" ON "job_distribution_records"("assignedAgentId");
CREATE INDEX "idx_job_distribution_records_createdAt" ON "job_distribution_records"("createdAt" DESC);

CREATE INDEX "idx_job_distribution_agents_jobDistributionId" ON "job_distribution_agents"("jobDistributionId");
CREATE INDEX "idx_job_distribution_agents_agentId" ON "job_distribution_agents"("agentId");
CREATE INDEX "idx_job_distribution_agents_workStatus" ON "job_distribution_agents"("workStatus");
CREATE INDEX "idx_job_distribution_agents_assignedAt" ON "job_distribution_agents"("assignedAt" DESC);

-- 为数组字段创建 GIN 索引以支持数组查询
CREATE INDEX "idx_agents_tags" ON "agents" USING GIN ("tags");
CREATE INDEX "idx_jobs_tags" ON "jobs" USING GIN ("tags");

-- 为 JSONB 字段创建 GIN 索引
CREATE INDEX "idx_jobs_budget" ON "jobs" USING GIN ("budget");
CREATE INDEX "idx_job_distribution_records_matchCriteria" ON "job_distribution_records" USING GIN ("matchCriteria");

-- 插入示例分类数据
INSERT INTO "categories" ("id", "title") VALUES 
('cuid_category_1', 'Data Analysis'),
('cuid_category_2', 'Web Development'),
('cuid_category_3', 'Machine Learning'),
('cuid_category_4', 'Content Creation'),
('cuid_category_5', 'Research');

-- 创建更新时间的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updatedAt" = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新 updatedAt 字段的表创建触发器
CREATE TRIGGER update_agents_updated_at 
    BEFORE UPDATE ON "agents" 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_jobs_updated_at 
    BEFORE UPDATE ON "jobs" 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- 添加数据完整性检查约束
ALTER TABLE "agents" ADD CONSTRAINT "check_reputation_range" 
CHECK ("reputation" >= 0.0 AND "reputation" <= 10.0);

ALTER TABLE "agents" ADD CONSTRAINT "check_success_rate_range" 
CHECK ("successRate" >= 0.0 AND "successRate" <= 1.0);

ALTER TABLE "agents" ADD CONSTRAINT "check_total_jobs_non_negative" 
CHECK ("totalJobsCompleted" >= 0);

ALTER TABLE "job_distribution_agents" ADD CONSTRAINT "check_progress_range" 
CHECK ("progress" IS NULL OR ("progress" >= 0 AND "progress" <= 100));

ALTER TABLE "job_distribution_agents" ADD CONSTRAINT "check_retry_count_non_negative" 
CHECK ("retryCount" >= 0);

ALTER TABLE "job_distribution_agents" ADD CONSTRAINT "check_execution_time_positive" 
CHECK ("executionTimeMs" IS NULL OR "executionTimeMs" > 0);

-- 添加评论说明
COMMENT ON TABLE "categories" IS '任务分类表';
COMMENT ON TABLE "agents" IS 'AI Agent 信息表，包含信誉系统和钱包集成';
COMMENT ON TABLE "jobs" IS '任务表，支持复杂预算结构和高级选项';
COMMENT ON TABLE "job_distribution_records" IS '任务分发记录表，记录程序分发逻辑';
COMMENT ON TABLE "job_distribution_agents" IS '任务分发执行表，跟踪每个Agent的执行状态';

COMMENT ON COLUMN "agents"."reputation" IS '信誉评分 (0.0-10.0)';
COMMENT ON COLUMN "agents"."successRate" IS '成功率 (0.0-1.0)';
COMMENT ON COLUMN "jobs"."budget" IS '预算，可以是数字或包含 min/max 的对象';
COMMENT ON COLUMN "job_distribution_agents"."executionResult" IS 'Agent执行结果，支持Markdown格式';
COMMENT ON COLUMN "job_distribution_agents"."progress" IS '执行进度百分比 (0-100)';

-- 验证数据库结构
SELECT 
    schemaname,
    tablename,
    tableowner
FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename IN ('categories', 'agents', 'jobs', 'job_distribution_records', 'job_distribution_agents')
ORDER BY tablename;

--  数据安全迁移
-- 只在表为空时进行迁移，避免数据丢失

-- 1. 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 2. 检查表是否为空
DO $$
DECLARE
    agents_count INTEGER;
    jobs_count INTEGER;
    job_distribution_records_count INTEGER;
    job_distribution_agents_count INTEGER;
BEGIN
    -- 检查各表的记录数
    SELECT COUNT(*) INTO agents_count FROM "agents";
    SELECT COUNT(*) INTO jobs_count FROM "jobs";
    SELECT COUNT(*) INTO job_distribution_records_count FROM "job_distribution_records";
    SELECT COUNT(*) INTO job_distribution_agents_count FROM "job_distribution_agents";
    
    -- 如果有数据，则报错
    IF agents_count > 0 OR jobs_count > 0 OR job_distribution_records_count > 0 OR job_distribution_agents_count > 0 THEN
        RAISE EXCEPTION '表中有数据，无法安全迁移。请先备份数据或清空表。';
    END IF;
    
    RAISE NOTICE '所有表为空，开始安全迁移...';
END $$;

-- 3. 删除所有相关的外键约束
ALTER TABLE "job_distribution_agents" 
DROP CONSTRAINT IF EXISTS "job_distribution_agents_agentId_fkey";

ALTER TABLE "job_distribution_agents" 
DROP CONSTRAINT IF EXISTS "job_distribution_agents_jobDistributionId_fkey";

ALTER TABLE "job_distribution_records" 
DROP CONSTRAINT IF EXISTS "job_distribution_records_jobId_fkey";

-- 4. 修改 agents 表的 id 字段为 UUID 类型
ALTER TABLE "agents" 
ALTER COLUMN "id" TYPE UUID USING (uuid_generate_v4()),
ALTER COLUMN "id" SET DEFAULT uuid_generate_v4();

-- 5. 修改 jobs 表的 id 字段为 UUID 类型
ALTER TABLE "jobs" 
ALTER COLUMN "id" TYPE UUID USING (uuid_generate_v4()),
ALTER COLUMN "id" SET DEFAULT uuid_generate_v4();

-- 6. 修改 job_distribution_records 表的 id 字段为 UUID 类型
ALTER TABLE "job_distribution_records" 
ALTER COLUMN "id" TYPE UUID USING (uuid_generate_v4()),
ALTER COLUMN "id" SET DEFAULT uuid_generate_v4();

-- 7. 修改 job_distribution_records 表的 jobId 字段为 UUID 类型
ALTER TABLE "job_distribution_records" 
ALTER COLUMN "jobId" TYPE UUID USING (uuid_generate_v4());

-- 8. 修改 job_distribution_agents 表的 id 字段为 UUID 类型
ALTER TABLE "job_distribution_agents" 
ALTER COLUMN "id" TYPE UUID USING (uuid_generate_v4()),
ALTER COLUMN "id" SET DEFAULT uuid_generate_v4();

-- 9. 修改 job_distribution_agents 表的 agentId 字段为 UUID 类型
ALTER TABLE "job_distribution_agents" 
ALTER COLUMN "agentId" TYPE UUID USING (uuid_generate_v4());

-- 10. 修改 job_distribution_agents 表的 jobDistributionId 字段为 UUID 类型
ALTER TABLE "job_distribution_agents" 
ALTER COLUMN "jobDistributionId" TYPE UUID USING (uuid_generate_v4());

-- 11. 重新添加外键约束
ALTER TABLE "job_distribution_records" 
ADD CONSTRAINT "job_distribution_records_jobId_fkey" 
FOREIGN KEY ("jobId") REFERENCES "jobs"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "job_distribution_agents" 
ADD CONSTRAINT "job_distribution_agents_jobDistributionId_fkey" 
FOREIGN KEY ("jobDistributionId") REFERENCES "job_distribution_records"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "job_distribution_agents" 
ADD CONSTRAINT "job_distribution_agents_agentId_fkey" 
FOREIGN KEY ("agentId") REFERENCES "agents"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- 12. 验证修改结果
SELECT 
    table_name, 
    column_name, 
    data_type 
FROM information_schema.columns 
WHERE table_name IN ('agents', 'jobs', 'job_distribution_records', 'job_distribution_agents')
    AND column_name IN ('id', 'agentId', 'jobId', 'jobDistributionId')
ORDER BY table_name, column_name;

DO $$
BEGIN
    RAISE NOTICE 'UUID 迁移完成！';
END $$; 

-- 修复所有表的updatedAt字段默认值

-- 1. 为agents表的updatedAt字段添加默认值
ALTER TABLE "agents" 
ALTER COLUMN "updatedAt" SET DEFAULT CURRENT_TIMESTAMP;

-- 2. 为jobs表的updatedAt字段添加默认值
ALTER TABLE "jobs" 
ALTER COLUMN "updatedAt" SET DEFAULT CURRENT_TIMESTAMP;

-- 3. 为job_distribution_records表的updatedAt字段添加默认值（如果存在）
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'job_distribution_records' 
        AND column_name = 'updatedAt'
    ) THEN
        ALTER TABLE "job_distribution_records" 
        ALTER COLUMN "updatedAt" SET DEFAULT CURRENT_TIMESTAMP;
    END IF;
END $$;

-- 4. 更新现有的NULL值
UPDATE "agents" 
SET "updatedAt" = CURRENT_TIMESTAMP 
WHERE "updatedAt" IS NULL;

UPDATE "jobs" 
SET "updatedAt" = CURRENT_TIMESTAMP 
WHERE "updatedAt" IS NULL;

-- 5. 验证修改结果
SELECT 
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name IN ('agents', 'jobs') 
    AND column_name = 'updatedAt'
ORDER BY table_name;

-- 6. 检查是否还有NULL值
SELECT 'agents' as table_name, COUNT(*) as null_count 
FROM "agents" 
WHERE "updatedAt" IS NULL
UNION ALL
SELECT 'jobs' as table_name, COUNT(*) as null_count 
FROM "jobs" 
WHERE "updatedAt" IS NULL;